﻿using aier.dataaccess.model;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
   public class CityDal
    {
        public DapperHelper dapperHelper { get; set; }

        /// <summary>
        /// 根据省查所属市
        /// </summary>
        /// <param name="provinceid">省份ID</param>
        /// <returns></returns>
        public List<CityView> QueryCityByProvinceId(Guid provinceid)
        {
            List<CityView> cityViews = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from new_city /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("new_province=@new_province");
                sqlParams.Add("@new_province", provinceid);
                cityViews = a.Query<CityView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return cityViews;
        }
        /// <summary>
        /// 根据市ID查询市信息
        /// </summary>
        /// <param name="cityid">市ID</param>
        /// <returns></returns>
        public CityView QueryCityById(Guid cityid)
        {
            CityView cityView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from new_city /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("new_cityId=@new_cityId");
                sqlParams.Add("@new_cityId", cityid);
                cityView = a.QueryFirstOrDefault<CityView>(sqlTemplate.RawSql, sqlParams);
            });

            return cityView;
        }
        /// <summary>
        /// 根据名称获取市ID
        /// </summary>
        /// <param name="new_name"></param>
        /// <returns></returns>
        public Guid GetCityIdByName(string new_name)
        {
            Guid CityId = Guid.Empty;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select new_cityId from new_cityBase /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(new_name))
                {
                    sqlBuilder.Where("new_name=@new_name");
                    sqlParams.Add("@new_name", new_name);
                }
                CityId = a.ExecuteScalar<Guid>(sqlTemplate.RawSql, sqlParams);
            });

            return CityId;
        }


        /// <summary>
        /// 根据市ID查询市信息
        /// </summary>
        /// <param name="cityid">市ID</param>
        /// <returns></returns>
        public string QueryCityNameById(Guid cityid)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select new_name from new_city /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("new_cityId=@new_cityId");
                sqlParams.Add("@new_cityId", cityid);
                return a.ExecuteScalar<string>(sqlTemplate.RawSql, sqlParams);
            });
        }
    }
}
